﻿use master
go
drop database SuportiveLearning
go
create database SuportiveLearning
go

use SuportiveLearning
go

create table [User]
(
	UserName varchar(30) primary key,
	Password varchar(32) not null,
	FirstName nvarchar(20) not null,
	LastName nvarchar(30) not null,
	Gender tinyint not null,
	Birthday datetime not null,
	Origin nvarchar(100),
	IdentityCard varchar(15) not null unique,
	Address nvarchar(100),
	LearningProcess nvarchar(2000),
	WorkExperience nvarchar(2000),
	Award nvarchar(1000),
	AdditionalInformation nvarchar(2000),
	Role varchar(10) not null,
	Status bit not null default 'true',--true:active;false:deactive
	Notes nvarchar(max)
)
go

create table BatchVersion
(
	ID varchar(10) primary key,
	DateCreate datetime not null default CURRENT_TIMESTAMP,
	[Description] nvarchar(max) not null,
	Images varchar(100),
	Duration tinyint not null, --Month
	TimeApplication datetime not null,
	Status bit not null default 'false'-- true: đang áp dụng, false:chưa áp dụng hoặc không còn áp dụng nữa; 
	
)
go

create table Subject
(
	ID varchar(7) primary key,
	Name nvarchar(150) not null,
	Description nvarchar(max),
	Status bit not null default 'true' --true:đang có trong chương trình dạy;false; đã bỏ khỏi chương trình dạy
)
go

create table BatchVersionSchedule
(
	BatchVersionID varchar(10),
	SubjectID varchar(7),
	Duration int not null, --số tiết dạy (mỗi tiết dạy sẽ có hoặc không có 1 assignment)
	primary key (BatchVersionID,SubjectID)
)
go

Create table XClass
(
	ClassName varchar(12) primary key,
	Startdate datetime not null,
	Status bit not null default 'true'--true:lớp đang học;false:lớp đã kết thúc
)
go

Create table ListStudent
(
	ClassName varchar(12),
	StudentID varchar(30),
	status bit not null default 'true',--true:sinh viên đang học trong lớp này;false:sinh viên không còn học trong lớp này
	primary key(ClassName,StudentID)
)
go

Create table TeachingSchedule
(
	ClassName varchar(12),
	SubjectID varchar(7),
	StartDate datetime,
	TeacherID varchar(30) not null,
	status bit not null default 'true',--true:normal;false:fail
	primary key(ClassName,SubjectID,StartDate)
)
go


Create table Assignment
(
	ID int identity(1,1) primary key,
	ClassName varchar(12) not null,
	SubjectID varchar(7) not null,
	IsExercise bit not null default 'true',--true: đây là bài tập bắt buộc làm;false:đây chỉ là tài liệu tham khảo
	Link varchar(300) not null,
	Note nvarchar(2000),
	TimePost datetime not null default CURRENT_TIMESTAMP,
	Deadline datetime not null,
	TeacherID varchar(30) not null,
	Updated tinyint not null default 0
)
go

Create table AssignmentComment
(
	ID int identity (1,1) primary key,
	AssignmentID int not null,
	Content nvarchar(1000) not null,
	Sender varchar(30) not null
)
go

Create table Feedback
(
	ID int identity (1,1) primary key,
	Sender varchar(30) not null,
	Title nvarchar(200) not null,
	Content nvarchar(2000) not null,
	Conversation nvarchar(max) not null,
	Recipients varchar(330) not null,
	IsPrivate bit not null default 'true'--true:chỉ người gửi(sinh viên) và những người nhận(teacher) mới thấy;false: tất cả mọi người trong lớp người gửi (students+teachers) đều thấy
)
go

Create table Submission
(	
	AssignmentID int,
	StudentID varchar(30),
	Link varchar(300) not null,
	Point float,
	Note nvarchar(1000) not null,
	primary key(AssignmentID,StudentID)
)
go


-----------------------------------
--BatchVersionSchedule
alter table BatchVersionSchedule
add 
constraint FK_BatchVersionSchedule_BatchVersion
foreign key (BatchVersionID)
references BatchVersion(ID)
go

alter table BatchVersionSchedule
add
constraint FK_BatchVersionSchedule_Subject
foreign key(SubjectID)
references Subject(ID)
go


-----------------------------------
--Assignment
alter table Assignment
add 
constraint FK_Assignment_XClass
foreign key (ClassName)
references XClass(ClassName)
go

alter table Assignment
add
constraint FK_Assignment_Subject
foreign key(SubjectID)
references Subject(ID)
go

alter table Assignment
add
constraint FK_Assignment_User
foreign key(TeacherID)
references [User](UserName)
go

-----------------------------------
--AssignmentComment

alter table AssignmentComment
add
constraint FK_AssignmentComment_Assignment
foreign key(AssignmentID)
references Assignment(ID)
go

alter table AssignmentComment
add
constraint FK_AssignmentComment_User
foreign key(Sender)
references [User](UserName)
go

-----------------------------------
--Feedback

alter table Feedback
add 
constraint FK_Feedback_User
foreign key(Sender)
references [User](UserName)
go

-----------------------------------
--ListStudent

alter table ListStudent
add
constraint ListStudent_XClass
foreign key(ClassName)
references XClass(ClassName)
go

alter table ListStudent
add
constraint ListStudent_User
foreign key(StudentID)
references [User](UserName)
go

-----------------------------------
--Submission

alter table Submission
add
constraint FK_Submission_Assignment
foreign key(AssignmentID)
references Assignment(ID)
go

alter table Submission
add
constraint FK_Submission_User
foreign key (StudentID)
references [User](UserName)
go

-----------------------------------
--Submission

alter table TeachingSchedule
add
constraint FK_TeachingSchedule_XClass
foreign key (ClassName)
references XClass(ClassName)
go

alter table TeachingSchedule
add
constraint FK_TeachingSchedule_Subject
foreign key(SubjectID)
references Subject(ID)
go

alter table TeachingSchedule
add
constraint FK_TeachingSchedule_User
foreign key(TeacherID)
references [User](UserName)
go